CREATE OR REPLACE TRIGGER ATS_T_LOTEMAT
  AFTER INSERT OR UPDATE on F_LOTEMAT FOR EACH ROW
BEGIN

    INSERT INTO ATS_LOTEMAT
    (LMT_CODLOT, LMT_LOCEST, LMT_CODPRO, LMT_INDICE, LMT_SEQIND, LMT_QTDLOT, LMT_SAILOT, 
     LMT_QTDDEV, LMT_LOTFOR, LMT_OBSERV, LMT_TIPLOT, LMT_ORIGEM)
    VALUES(:NEW.LMT_CODLOT, :NEW.LMT_LOCEST, :NEW.LMT_CODPRO, :NEW.LMT_INDICE, :NEW.LMT_SEQIND, :NEW.LMT_QTDLOT, :NEW.LMT_SAILOT, 
           :NEW.LMT_QTDDEV, :NEW.LMT_LOTFOR, :NEW.LMT_OBSERV, :NEW.LMT_TIPLOT, 1);
            
  EXCEPTION 
  WHEN DUP_VAL_ON_INDEX THEN
      UPDATE ATS_LOTEMAT 
         SET LMT_CODPRO = :NEW.LMT_CODPRO, 
             LMT_INDICE = :NEW.LMT_INDICE, 
             LMT_SEQIND = :NEW.LMT_SEQIND, 
             LMT_QTDLOT = :NEW.LMT_QTDLOT, 
             LMT_SAILOT = :NEW.LMT_SAILOT, 
             LMT_QTDDEV = :NEW.LMT_QTDDEV, 
             LMT_LOTFOR = :NEW.LMT_LOTFOR, 
             LMT_OBSERV = :NEW.LMT_OBSERV, 
             LMT_TIPLOT = :NEW.LMT_TIPLOT, 
             LMT_ORIGEM = 1
      WHERE LMT_CODLOT = :NEW.LMT_CODLOT 
      AND   LMT_LOCEST = :NEW.LMT_LOCEST;                         

END;
/

